<?php
/*
 *  This file is part of Urd.
 *
 *  Urd is free software; you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation; either version 3 of the License, or
 *  (at your option) any later version.
 *  Urd is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program. See the file "COPYING". If it does not
 *  exist, see <http://www.gnu.org/licenses/>.
 *
 * $LastChangedDate: 2008-07-18 19:15:03 +0200 (Fri, 18 Jul 2008) $
 * $Rev: 1304 $
 * $Author: gavinspearhead $
 * $Id: db.class.php 1304 2008-07-18 17:15:03Z gavinspearhead $
 */
 
// This is an include-only file:
if (!defined('ORIGINAL_PAGE')) die('This file cannot be accessed directly.');

$pathdbc = realpath(dirname(__FILE__));

require_once "$pathdbc/../config.php";
require_once "$pathdbc/defines.php";
require_once "$pathdbc/adodb/adodb-exceptions.inc.php";
require_once "$pathdbc/adodb/adodb.inc.php";

/* Table interface: */
class TableSetData
{
	public $setID;
	public $groupID;
	public $subject;
	public $articlesmax;
	public $binaries;
	public $date;
	public $size;
}

class TableBinaries
{
	public $binaryID;
	public $subject;
	public $fromname;
	public $date;
	public $bytes;
	public $totalparts;
	public $setID;
}

class TableParts
{
	public $binaryID;
	public $messageID;
	public $subject;
	public $fromname;
	public $date;
	public $partnumber;
	public $size;
	//public $groupID;
}

class TableGroups
{
    /* Todo */
}


/* Database interface */
class DatabaseConnection
{
	private $databasetype;
	private $databasename;
	private $hostname;
	private $DB;
	private $username;
	private $password;

	private $force_new_connection;
	private $result;

	function __construct ($databasetype, $hostname, $user, $pass, $database, $force_new_connection=FALSE)
    {
        global $ADODB_COUNTRECS;
//		$ADODB_COUNTRECS = FALSE; // ensures that rows are not counted as they are inserted
		assert (is_bool($force_new_connection));
		$this->databasetype = $databasetype; 
		$this->databasename = $database;
		$this->hostname = $hostname;
		$this->username = $user;
		$this->password = $pass;
		$this->force_new_connection = $force_new_connection;
		// We do store the sensitive information.  ;-)
		$this->connect();
		$this->DB->SetFetchMode(ADODB_FETCH_ASSOC); // don't do $res[0][0] ,but only $res[0]['name'];
	}

	function __destruct()
    {
		$this->disconnect();
	}

	function disconnect()
	{
		$this->DB->close();
	}
	
	function get_pattern_search_command($search_type)
	{
		$search_type = strtoupper($search_type);
		switch($this->databasetype){
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			if ($search_type == 'LIKE')
				return 'LIKE';
			elseif ($search_type == 'REGEXP')
				return 'REGEXP';
			else 
				throw new exception ('Unknown search type');
			break;
		case 'postgres8':
		case 'postgres7':
			if ($search_type == 'LIKE')
				return '~~*';
			elseif ($search_type == 'REGEXP')
				return '~*';
			else 
				throw new exception ('Unknown search type');
			break;
		default:
			throw new exception ('Database type no supported');
		}
	}

	function set_fetch_mode($mode = ADODB_FETCH_DEFAULT)
	{
		return $this->DB->SetFetchMode($mode);
	}

	function drop_database($dbase)
    {
		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$sql = "DROP DATABASE IF EXISTS \"$dbase\"";
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
			$sql = "DROP DATABASE \"$dbase\"";
			$this->DB->execute($sql);
			break;
		case 'sqlite':
			// do nothing; creation is done by opening file
			break;
		default:
			throw new exception ('Database type no supported');
		}
	}

	function drop_user($user, $host='localhost')
    {
        $this->escape($user);
		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$host = ($host == 'localhost') ? 'localhost' : '%';
			$sql = 'DROP USER "' . $user . '"@' . $host;
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
			$sql = 'DROP USER ' . $user;
			$this->execute_query($sql);
			break;
		case 'sqlite':
			// do nothing; creation is done by opening file
			break;
		default:
			throw new exception ('Database type no supported');
		}
	}

	function create_dbuser($host, $user, $pass)
	{
        $this->escape($user);
        $this->escape($host);
        $this->escape($pass);
		$host = ($host == 'localhost') ? 'localhost' : '%';

		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$sql = "CREATE USER \"$user\"@\"$host\" IDENTIFIED BY '$pass'";
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
			$sql = "CREATE USER \"$user\" WITH PASSWORD '$pass'";
			$this->execute_query($sql);
			break;
		case 'sqlite':
			// do nothing; creation is done by opening file
			break;
		default:
			throw new exception ('Database type no supported');
		}
	}

	function grant_rights($host, $dbname, $dbuser)
	{
        $host = ($host == 'localhost') ? 'localhost' : '%';
        $this->escape($host);
        $this->escape($dbname);
        $this->escape($dbuser);

		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$sql = "GRANT ALL ON \"$dbname\".* TO \"$dbuser\"@\"$host\"";
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
            $sql = "ALTER DATABASE  \"$dbname\" OWNER TO \"$dbuser\"";
			$this->execute_query($sql);
			// xxx not needed????
			break;

		case 'sqlite':
			// do nothing; creation is done by opening file
			break;
		default:
			throw new exception ('Database type no supported');
		}
	}

	function create_database($dbname)
	{
        $this->escape($dbname);
		switch($this->databasetype) {
		case 'mysql':
		case 'mysqlt':
		case 'mysqli':
			$sql = "CREATE DATABASE \"$dbname\" DEFAULT CHARSET \"utf8\" DEFAULT COLLATE \"utf8_general_ci\"";
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
			$sql = "CREATE DATABASE \"$dbname\"";
            $this->execute_query($sql);
			break;
		case 'sqlite':
		default:
			throw new exception ('Database type no supported');
		}
	}

	function optimise_table($table)
	{
        $this->escape($table);
		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$sql = 'OPTIMIZE TABLE "' . $table . '"';
			$this->execute_query($sql);
			$sql = 'ANALYZE TABLE "' . $table . '"';
			$this->execute_query($sql);
			break;
		case 'postgres8':
		case 'postgres7':
			if ($table == '__ALL__')
				$sql = 'VACUUM FULL ANALYZE ';
			else 
				$sql = 'VACUUM FULL ANALYZE "' . $table . '"';
			$this->execute_query($sql);
			break;
		case 'sqlite':
		default:
			throw new exception ('Database type no supported');
		}
	}

	function toggle_index($table, $on= FALSE)
	{
		assert (is_bool($on) && is_string($table));
        $this->escape($table);
		switch($this->databasetype) {
		case 'mysql':
		case 'mysqli':
		case 'mysqlt':
			$enable = $on ? 'ENABLE':'DISABLE';
			$this->execute_query("ALTER TABLE \"$table\" $enable KEYS");
			break;
		case 'postgres8':
		case 'postgres7':
			// XXX TODO
			break;
		case 'sqlite':
		default:
			throw new exception ('Database type no supported');
		}
	}
	
    function qstr($val) 
    { 
        return $this->DB->qstr($val); 
    }
    private function execute_if_exists($table, $qry)
    {
		switch($this->databasetype) {
			case 'mysql':
			case 'mysqli':
            case 'mysqlt':
                // to do -- not needed?
                return FALSE;
                break;
			case 'postgres8':
			case 'postgres7':
                $sql = "SELECT \"relname\" FROM pg_class WHERE relname = '$table';";
                $res = $this->execute_query($sql);
                if ($res !== FALSE)
                    return $this->execute_query($qry);
                else
                    return FALSE;
            default:
                return FALSE;
        }
    }
	function get_tables() 
	{ 
		switch($this->databasetype) {
			case 'mysql':
			case 'mysqli':
			case 'mysqlt':
				return $this->execute_query('SHOW TABLES'); 
			case 'postgres8':
			case 'postgres7':
				return array (0=>array(0=>'__ALL__')); // XXX postfix doesn't have a show tables equivalent. We use a trick here: we return all and for optimising we run the vacuum full without a table name. Nasty? yes. Works? yes.
			default:
				throw new exception ('Database type no supported');
		}
	}

	function connect()
	{
		echo_debug("Connecting to {$this->databasetype} @ {$this->hostname}", DEBUG_DATABASE);
		try {
			switch (strtolower($this->databasetype)) {
				case 'postgres8':
				case 'postgres7':
					$this->DB = NewADOConnection($this->databasetype);
					if (!$this->force_new_connection)
						$this->DB->Connect($this->hostname, $this->username, $this->password, $this->databasename);
					else
						$this->DB->NConnect($this->hostname, $this->username, $this->password, $this->databasename);
					break;
				case 'mysql':
				case 'mysqli':
				case 'mysqlt':
					$this->DB = NewADOConnection($this->databasetype);
					if (!$this->force_new_connection)
						$this->DB->Connect($this->hostname, $this->username, $this->password, $this->databasename);
					else
						$this->DB->NConnect($this->hostname, $this->username, $this->password, $this->databasename);
					$this->execute_query("SET sql_mode='ANSI_QUOTES'"); //Needed so we can use the same queries on postgres and mysql
					break;
				case 'sqlite':
					throw new exception ("Database type {$this->databasetype} not yet supported");
					$this->DB = NewADOConnection($this->databasetype);
					if (!$this->force_new_connection)
						$this->DB->Connect($this->hostname);
					else
						$this->DB->NConnect($this->hostname);
					break;
				case 'postgres':
				case 'postgres64':
				default :
					throw new exception ("Database type {$this->databasetype} not yet supported");
					break;
			}
		} catch (exception $e) {
			throw new exception('Could not connect to database : ' . $e->getMessage());
		}
	}

	function execute_query($query, $values=FALSE) 
	{
		$this->result = NULL;
		if (!$this->DB->isConnected())
			$this->connect();
		try {	    
			$this->result = $this->DB->Execute($query, $values);
		} catch (exception $e) {
			try {
				$this->connect();
				$this->result = $this->DB->Execute($query, $values);
			}  catch (exception $e) {
				throw new exception("Could not execute SQL query \"$query\" " . $e->getMessage());
			}
		}
		if ($this->result->RecordCount() == 0 )
			return FALSE;
		else {
			$this->result->Move(0); // just to be sure
			return $this->result->GetArray();
		}
	}

	function update_query($table, array $columns, array $values, $where)
	{
		$col_str = $val_str = '';
		reset($columns);
		reset($values);
		do {
			$c = current($columns);
			$v = current($values);
			if ($c === FALSE && $v === FALSE)
				break;
			if (($c === FALSE || $v === FALSE)) 
				throw new exception ('Could not execute update query; columns and values do not match');
			$this->escape($v, TRUE);

			$col_str .= "\"$c\" = $v, ";

			next($columns); 
			next($values);
		} while (TRUE);
		$col_str = rtrim($col_str, ', ');
		$query = "UPDATE $table SET $col_str WHERE $where";
		$this->execute_query($query);
	}


	function insert_query($table, array $columns, array $values, $get_last_ID=TRUE)
	{
	/* values can be an array of values or an array of an array of values
	   array (1, 2, 3) or
	   array (array(1, 2, 4), array (3, 4, 5)) each inside array is one row
	 */
		assert(is_string($table));
		$col_str = $val_str = '';
		
		foreach ($columns as $col) {
			$col_str .= "\"$col\", ";
			$val_str .= '?, ';
		}
		$col_str = rtrim($col_str, ', ');
		$val_str = rtrim($val_str, ', ');
		$sql = "INSERT INTO $table ($col_str) VALUES ($val_str)";
		$p_sql = $this->DB->prepare($sql);
		try {
            $this->execute_query($p_sql, $values);
            if ($get_last_ID)
                return $this->DB->Insert_ID();
            else
                return FALSE;
		} catch (exception $e) {
			throw new exception("Could not execute SQL query \"$sql\" " . $e->getMessage());
		}
	}

    function lock(array $tableactions)
    {
        switch ($this->databasetype) {
        case 'mysql':
        case 'mysqlt':
        case 'mysqli':
            $qry = '';
            foreach ($tableactions as $table => $action)
                $qry .= "$table $action, ";

            $qry = rtrim($qry, ' ,');
            $this->execute_query('LOCK TABLE ' . $qry);
            break;
        case 'postgres8':
        case 'postgres7':
            $this->execute_query('BEGIN WORK');
            foreach ($tableactions as $table => $action) { // XXX todo translate mysql locks to pg --> read, write, ...
                $this->execute_query("LOCK TABLE $table IN EXCLUSIVE MODE");
            }
            break;
        default:
            throw new exception ('Database type no supported');
            break;
        }
    }

    function unlock() 
    { 
        switch ($this->databasetype) {
        case 'mysql':
        case 'mysqli':
        case 'mysqlt':
            return $this->execute_query('UNLOCK TABLE'); 
        case 'postgres8':
        case 'postgres7':
            return $this->execute_query('COMMIT WORK');
        default:
            throw new exception ('Database type no supported');
            break;
        }
    }
   function escape(&$object, $quoted = FALSE)
    {
        assert(is_bool($quoted));
        if (is_array($object) || is_object($object)) {
            foreach ($object as &$item) 
                $this->escape($item, $quoted);
        } else {
            $object = $this->DB->qstr($object);
            if (!$quoted) 
                $object = substr($object, 1, - 1);
        }
    }

    function num_rows($result = FALSE)
    {
        if ($result === FALSE) 
            return $this->result->RecordCount();
        else 
            return $result->RecordCount();
    }

    function get_last_id() 
    { 
        switch ($this->databasetype) {
        case 'mysql':
        case 'mysqli':
        case 'mysqlt':
            return $this->DB->Insert_ID(); 
            break;
        case 'postgres8':
        case 'postgres7':
            $res = $this->execute_query('SELECT lastval() AS lv');
            return $res[0]['lv'];
            break;
        case 'sqlite':
        default:
            throw new exception ('Database type no supported');
            break;
        }

    }
	function select_query($qry, $num_rows=-1, $offset=-1)
	{
		assert(is_numeric($num_rows) && is_numeric($offset) && is_string($qry));
		$this->result = NULL;
		$sql = "SELECT $qry";
		if (!$this->DB->isConnected())
			$this->connect();
		try {
			$this->result = $this->DB->SelectLimit($sql, $num_rows, $offset);
		} catch (exception $e) {
			throw new exception("Could not execute SQL query \"$sql\" " . $e->getMessage());
		}
		if ($this->result->RecordCount() == 0) {
			return FALSE;
		} else {
			$this->result->Move(0); // just to be sure
			return $this->result->GetArray();
		}
	}

	function add_parts(array $tables, $groupID)
	{
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        assert(is_numeric($groupID));

		// All group ID's should be identical in a batch:
		if (count($tables) == 0) 
			return;
		$x = 1;
		static $cols = array('binaryID', 'messageID', 'subject', 'fromname', 'date', 'partnumber', 'size', 'dirty');
		$vals = array();

        $this->lock(array("parts_$groupID" => 'write'));
        try {
            foreach ($tables as $table) {
                if ($x % MAX_INSERT_PARTS == 0) {
                    $this->insert_query("parts_$groupID", $cols, $vals, FALSE);
                    $vals = array();
                }
                $vals[] = array($table->binaryID, $table->messageID, $table->subject, $table->fromname, $table->date, $table->partnumber, $table->size, DIRTY);
                ++$x;
            }
            // Insert the remaining values (if any):
            if (count($vals) > 0) 
                $this->insert_query("parts_$groupID", $cols, $vals, FALSE);
        } catch (exception $e) {
            $this->unlock();
            throw $e;
        }
        $this->unlock();
    }


    function update_binary_data($groupID, $id)
    {
        assert (is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $this->escape($groupID);
        $sql = "SELECT * FROM groups WHERE \"ID\" = '$groupID'";
        $res = $this->execute_query($sql);
        if ($res === FALSE || !isset($res[0]['name'])) 
            throw new exception("Unknown groupID : $groupID");
        $groupname = $res[0]['name'];
        $nrofrows = $res[0]['postcount']; // Get total nr rows in table, safer would be to do a count and not trust groups table data
        $stepsize = GENSETS_STEPSIZE;
        //$stepsize = 50;  //TEMP FIXME
        $sql = " count(*) AS total FROM (SELECT DISTINCT \"binaryID\" FROM parts_$groupID WHERE \"dirty\" = " . DIRTY . ") AS t";
        $res = $this->select_query($sql);
        if (!isset($res[0]['total'])) {
            write_log('No parts found', LOG_NOTICE);
            return;
        }
        $total = $res[0]['total'];
        update_queue_status($this, $id, NULL, NULL, 1, NULL, NULL);
        write_log("Timestamping.  Total is $total", LOG_NOTICE);
        $offset = 0;
        $cnt = 0;
        static $cols = array ('binaryID', 'subject', 'fromname', 'date', 'bytes', 'totalParts', 'setID', 'dirty');
        //        $this->execute_query("ANALYZE TABLE parts_$groupID"); // doesn't work in psql XXX fixit

        // Here we go, another try to get lightning fast genset ;) We start with some defines, should be moved to defines.php if this scheme works

        update_queue_status($this, $id, NULL, NULL, 2, NULL, NULL);
        $s_time = microtime(TRUE);
        while (1) {  // Loop through every chunk in turn to benefit caching -> no tmp tables on disk
            $sql = "DISTINCT \"binaryID\" FROM parts_$groupID WHERE \"dirty\" != 0"; // alternative, use offset in the select
            $res = $this->select_query($sql, $stepsize);
            if (!is_array($res)) {
                echo_debug("Processed $cnt binaries.", DEBUG_SERVER);
                return;
            }
            $cnt += count($res);
            $l = '';
            foreach ($res as $row) 
                $l .= "'{$row['binaryID']}',";
            $l = rtrim($l, ', ');

            $this->execute_query("DELETE FROM binaries_$groupID WHERE \"binaryID\" IN ($l)");  // We may hit "the future" here etc
            $sql = "SUM(\"size\") AS totalsize, \"binaryID\", COUNT(*) AS parttotal, MAX(\"subject\") AS subject, MAX(\"fromname\") AS fromname, MIN(\"date\") AS mindate " // max for subject and fromname are a bit weird but needed
                . " FROM parts_$groupID WHERE \"binaryID\" IN ($l) GROUP BY \"binaryID\""; // We may hit "the future" here, but that is OK as it's marked dirty anyway
            $res = $this->select_query($sql, $stepsize);
            $vals = array();
            // For each row, export to binaries_X
            foreach ($res as $row) {

                // Determine SetID:
                $subject = $row['subject'];
                $poster = $row['fromname'];

                $cntFull = get_set_size($subject);

                // First 3 characters of the poster are also used:
                $poster = preg_replace('/([^a-z]+)/i', '', substr($poster, 0, 10));
                $poster = strtolower(substr($poster, 0, 3));

                // Try to get a upload-dependent subject (let's call it a dlname):
                $subject = trim($subject);

                // If there is an actual filename, remove it (we don't want filename specific sets (.nfo, .jpg, .rar, .par2 etc))
                if (preg_match('/"(.*?)"/', $subject, $vars)) {// Let's hope there is only 1 set of " "'s...
                    $file = $vars[1];
                    $cleansubject = str_replace($file, '', $subject);
                } else {
                    $file = '';
                    $cleansubject = $subject;
                }

                // Also, we do not want .001 / .002 in our set name:
                $cleansubject = preg_replace('|\.(\d{3})|', '', $cleansubject);

                if (preg_match("|(.*?)[[(]{1}[0-9]+[/\\ of]+[0-9]+|", $cleansubject, $vars)) {
                    // subject up till the first ( or [, followed by a number and a delimiter (e.g. [30/480]):
                    $dlname = $vars[1];
                } else if (preg_match('/(.*?)\d+[^0-9]+/', $cleansubject, $vars)) {
                    // subject up till the first number, unless the number is at the end (ie '.mp3' or @192):
                    $dlname = $vars[1];
                } else {
                    // The first 20 characters of the subject:
                    $dlname = substr($cleansubject, 0, 20);
                }

                // Sanity check: Downloadname is VERY short, and we have a filename? Use the first part of the filename:
                if (strlen($dlname) < 20 && $file != '') 
                    $dlname = substr($file, 0, 10);

                // Hash it, so we can shorten the length of $dlname without possibly missing the unique part
                // (Think "#Alt.Binaries.Movies.XviD On EFNet Presents: Rambo.V.REAL.PROPER.R5.xVID-UNiVERSAL",
                //   if we take the first 20 characters, a lot of uploads will be merged to 1 set)
                // Also: Can't use groupID as it's newsserver specific, need to use the group name. (We do need a group identifier or x-posts will be merged)

                // This is it:
                $setID = md5($dlname . $groupname . $poster . $cntFull);

                // Not DELAYED because then generating sets will fail; as the binaries aren't there yet
                $vals[] = array ($row['binaryID'], $row['subject'], $row['fromname'], $row['mindate'], $row['totalsize'], $row['parttotal'], $setID, DIRTY);

                if ((count($vals) % MAX_INSERT_PARTS) == 0) {
                    $this->insert_query('binaries_' . $groupID, $cols, $vals, FALSE);
                    $vals = array();
                }

            }
            if (count($vals) > 0) {
                $this->insert_query('binaries_' . $groupID, $cols, $vals);
            }
            $sql = "UPDATE parts_$groupID SET \"dirty\" = " . CLEAN . " WHERE \"binaryID\" IN ($l)";  
            $this->execute_query($sql);

            $t_time = microtime(TRUE);
            if ($cnt > 0 && $total > 0) {
                $ETA = floor((($total - $cnt) * ($t_time - $s_time) / $cnt) / .75);
                $progress = floor((75 * $cnt) / $total);
                update_queue_status($this, $id, NULL, $ETA, $progress, NULL, NULL);
            }
        }
    }
/*

    function update_binary_data_old($groupID, $id)
    {
        assert (is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $this->escape($groupID);
        $sql = "SELECT * FROM groups WHERE \"ID\" = '$groupID'";
        $res = $this->execute_query($sql);
        if ($res === FALSE || !isset($res[0]['name'])) 
            throw new exception("Unknown groupID : $groupID");
        $groupname = $res[0]['name'];
        $stepsize = GENSETS_STEPSIZE;
        //        $sql = " count(*) AS total FROM (SELECT \"binaryID\" FROM parts_$groupID WHERE \"dirty\" != 0 GROUP BY \"binaryID\") AS t";
        $sql = "count(DISTINCT \"binaryID\") AS total FROM parts_$groupID WHERE \"dirty\" != 0";
        $res = $this->select_query($sql);
        if (!isset($res[0]['total'])) {
            write_log('No parts found', LOG_NOTICE);
            return;
        }
        $total = $res[0]['total'];
        $offset = 0;
        $cnt = 0;
        while (1) {
            //$sql = " \"binaryID\" FROM parts_$groupID WHERE \"dirty\" != 0 GROUP BY \"binaryID\"";
            //$sql = " \"binaryID\" FROM parts_$groupID WHERE \"dirty\" != 0 ";
            $sql = "SUM(\"size\") AS totalsize, \"binaryID\", COUNT(*) AS parttotal, MAX(\"subject\") AS subject, MAX(\"fromname\") AS fromname, MIN(\"date\") AS mindate " // max for subject and fromname are a bit weird but needed
                . " FROM parts_$groupID GROUP BY \"binaryID\" HAVING MAX(\"dirty\") > 0";
            $res = $this->select_query($sql, $stepsize);
            if (!is_array($res) && $offset == 0) {
                //  $this->unlock();
                write_log("No new parts found for group with id $groupID!", LOG_NOTICE);
                return;
            } else if (!is_array($res)) {
                echo_debug("Processed $cnt binaries. Total was ($total)", DEBUG_SERVER);
                break;
            }

            $offset += $stepsize;
            $l = '';
            $b_id = array();
            
            foreach ($res as $row) {
                //if (!isset($b_id[$row['binaryID']]))
                 //   $b_id[$row['binaryID']] = $row['binaryID'];
                    $l .= "'{$row['binaryID']}',";
            }
//                $l .= "'{$row['binaryID']}',";
            $l = rtrim($l, ', ');
            $cnt += count($b_id);

    //      $this->execute_query("DELETE FROM binaries_$groupID WHERE \"binaryID\" IN ($l)");
            $res = $this->select_query($sql);
            // For each row, export to binaries_X
            foreach ($res as $row) {
                // Determine SetID:
                $subject = $row['subject'];
                $poster = $row['fromname'];

                $cntFull = get_set_size($subject);

                // First 4 characters of the poster are also used:
                $poster = preg_replace('/([^a-z]+)/i', '', substr($poster, 0, 10));
                $poster = strtolower(substr($poster, 0, 3));

                // Try to get a upload-dependent subject (let's call it a dlname):
                $subject = trim($subject);

                // If there is an actual filename, remove it (we don't want filename specific sets (.nfo, .jpg, .rar, .par2 etc))
                if (preg_match('/"(.*?)"/', $subject, $vars)) {// Let's hope there is only 1 set of " "'s...
                    $file = $vars[1];
                    $cleansubject = str_replace($file, '', $subject);
                } else {
                    $file = '';
                    $cleansubject = $subject;
                }

                // Also, we do not want .001 / .002 in our set name:
                $cleansubject = preg_replace('|\.(\d{3})|', '', $cleansubject);

                if (preg_match("|(.*?)[[(]{1}[0-9]+[/\\ of]+[0-9]+|", $cleansubject, $vars)) {
                    // subject up till the first ( or [, followed by a number and a delimiter (e.g. [30/480]):
                    $dlname = $vars[1];
                } else if (preg_match('/(.*?)\d+[^0-9]+/', $cleansubject, $vars)) {
                    // subject up till the first number, unless the number is at the end (ie '.mp3' or @192):
                    $dlname = $vars[1];
                } else {
                    // The first 20 characters of the subject:
                    $dlname = substr($cleansubject, 0, 20);
                }

                // Sanity check: Downloadname is VERY short, and we have a filename? Use the first part of the filename:
                if (strlen($dlname) < 20 && $file != '') 
                    $dlname = substr($file, 0, 10);

                // Hash it, so we can shorten the length of $dlname without possibly missing the unique part
                // (Think "#Alt.Binaries.Movies.XviD On EFNet Presents: Rambo.V.REAL.PROPER.R5.xVID-UNiVERSAL",
                //   if we take the first 20 characters, a lot of uploads will be merged to 1 set)
                // Also: Can't use groupID as it's newsserver specific, need to use the group name. (We do need a group identifier or x-posts will be merged)

                // This is it:
                $setID = md5($dlname . $groupname . $poster . $cntFull);

                // Not DELAYED because then generating sets will fail; as the binaries aren't there yet
                $this->execute_query("DELETE FROM binaries_$groupID WHERE \"binaryID\" = '{$row['binaryID']}'");
                static $cols = array ('binaryID', 'subject', 'fromname', 'date', 'bytes', 'totalParts', 'setID', 'dirty');
                $vals = array ($row['binaryID'], $row['subject'], $row['fromname'], $row['mindate'], $row['totalsize'], $row['parttotal'], $setID, DIRTY);
                $this->insert_query('binaries_' . $groupID, $cols, $vals);
                $sql = "UPDATE parts_$groupID SET \"dirty\" = 0 WHERE \"binaryID\" = '{$row['binaryID']}'";
                $this->execute_query($sql);
            }
//            $sql = "UPDATE parts_$groupID SET \"dirty\" = 0 WHERE \"binaryID\" IN ($l)";
            //$this->execute_query($sql);
            update_queue_status($this, $id, NULL, NULL, floor((75 * $cnt) / $total), NULL, NULL);
        }
         //   $sql = "UPDATE parts_$groupID SET \"dirty\" = 0 WHERE \"dirty\" != 0 ";
          //  $this->execute_query($sql);
    }
 */
    //old
    /*
    function add_binary_data($groupID)
    {
        assert (is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        // First delete everything for this group:
        $this->escape($groupID);
        $sql = "TRUNCATE binaries_$groupID";
        $this->execute_query($sql);
        $sql = "SELECT * FROM groups WHERE \"ID\" = '$groupID'";
        $res = $this->execute_query($sql);
        if ($res === FALSE || !isset($res[0]['name'])) 
            throw new exception("Unknown groupID : $groupID");
        $groupname = $res[0]['name'];

        $cnt = 0;
        $stepsize = GENSETS_STEPSIZE;
        $lastkey = 0;
        //    $this->lock(array("binaries_$groupID" => 'write', "parts_$groupID" => 'read'));
        try {
            while (1) {
                // Generate the data that goes into the binary table:
                $sql = "SUM(\"size\") AS totalsize, \"binaryID\", COUNT(*) AS parttotal, MAX(\"subject\") AS subject, MAX(\"fromname\") AS fromname, MIN(\"date\") AS mindate " // max for subject and fromname are a bit weird but needed
                    . " FROM parts_$groupID WHERE \"binaryID\" > '$lastkey' GROUP BY \"binaryID\" ORDER BY \"binaryID\" ";
                $res = $this->select_query($sql, $stepsize);
                if (!is_array($res) && $cnt == 0) {
                    //  $this->unlock();
                    write_log("No parts found for group with id $groupID!", LOG_NOTICE);
                    return;
                } else if (!is_array($res)) {
                    echo_debug('done', DEBUG_SERVER);
                    break;
                }
                $cnt += count($res);
                // For each row, export to binaries_X
                foreach ($res as $row) {
                    // Determine SetID:
                    $subject = $row['subject'];
                    $poster = $row['fromname'];

                    $cntFull = get_set_size($subject);

                    // First 4 characters of the poster are also used:
                    $poster = preg_replace('/([^a-z]+)/', '', strtolower(substr($poster, 0, 10)));
                    $poster = substr($poster, 0, 3);

                    // Try to get a upload-dependent subject (let's call it a dlname):
                    $subject = trim($subject);

                    // If there is an actual filename, remove it (we don't want filename specific sets (.nfo, .jpg, .rar, .par2 etc))
                    if (preg_match('/"(.*?)"/', $subject, $vars)) {// Let's hope there is only 1 set of " "'s...
                        $file = $vars[1];
                        $cleansubject = str_replace($file, '', $subject);
                    } else {
                        $file = '';
                        $cleansubject = $subject;
                    }

                    // Also, we do not want .001 / .002 in our set name:
                    $cleansubject = preg_replace('|\.(\d{3})|', '', $cleansubject);

                    if (preg_match("|(.*?)[[(]{1}[0-9]+[/\\ of]+[0-9]+|", $cleansubject, $vars)) {
                        // subject up till the first ( or [, followed by a number and a delimiter (e.g. [30/480]):
                        $dlname = $vars[1];
                    } else if (preg_match('/(.*?)\d+[^0-9]+/', $cleansubject, $vars)) {
                        // subject up till the first number, unless the number is at the end (ie '.mp3' or @192):
                        $dlname = $vars[1];
                    } else {
                        // The first 20 characters of the subject:
                        $dlname = substr($cleansubject, 0, 20);
                    }

                    // Sanity check: Downloadname is VERY short, and we have a filename? Use the first part of the filename:
                    if (strlen($dlname) < 20 && $file != '') 
                        $dlname = substr($file, 0, 10);

                    // Hash it, so we can shorten the length of $dlname without possibly missing the unique part
                    // (Think "#Alt.Binaries.Movies.XviD On EFNet Presents: Rambo.IV.REAL.PROPER.R5.xVID-UNiVERSAL",
                    //   if we take the first 20 characters, a lot of uploads will be merged to 1 set)
                    // Also: Can't use groupID as it's newsserver specific, need to use the group name. (We do need a group identifier or x-posts will be merged)

                    // This is it:
                    $setID = md5($dlname . $groupname . $poster . $cntFull);

                    // Not DELAYED because then generating sets will fail; as the binaries aren't there yet
                    static $cols = array ('binaryID', 'subject', 'fromname', 'date', 'bytes', 'totalParts', 'setID');
                    $vals = array ($row['binaryID'], $row['subject'], $row['fromname'], $row['mindate'], $row['totalsize'], $row['parttotal'], $setID);
                    $this->insert_query('binaries_' . $groupID, $cols, $vals);
                    $lastkey = $row['binaryID'];
                }
            }
        } catch(exception $e) {
            //  $this->unlock();
            throw $e;
        }
        //   $this->unlock();
    }
     */
    function update_set_data($groupID, $id)
    {
        assert(is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $this->escape($groupID);
            
        $stepsize = GENSETS_STEPSIZE;
        $offset = 0;
        $cnt = 0;
        $sql = "SELECT count(*) AS total FROM (SELECT DISTINCT \"setID\" FROM binaries_$groupID WHERE \"dirty\" = " . DIRTY . ") AS t";
        $res = $this->execute_query($sql);
        if (!isset($res[0]['total'])) {
            write_log('No binaries found', LOG_NOTICE);
            return;
        }
        $total = $res[0]['total'];
        $s_time = microtime(TRUE);
        while (1) {
            $sql = " DISTINCT \"setID\" FROM binaries_$groupID WHERE \"dirty\" = " . DIRTY;
            $res = $this->select_query($sql, $stepsize);
            if (!is_array($res) && $offset == 0) {
                write_log("No new binaries found for group with id $groupID!", LOG_NOTICE);
                return;
            } else if (!is_array($res)) {
                echo_debug("Done. Processed $cnt sets. Total was $total", DEBUG_SERVER);
                break;
            }
            $cnt += count($res);

            $l = '';
            foreach ($res as $row) 
                $l .= "'{$row['setID']}',";
            $l = rtrim($l, ', ');

            $this->execute_query("DELETE FROM setdata WHERE \"ID\" IN ($l) AND \"groupID\" = '$groupID'");
            $sql = ' "setID", count("binaryID") AS bins, MIN("subject") AS subject, MIN("date") AS date, SUM("bytes") AS totalsize ' .
                "FROM \"binaries_$groupID\" WHERE \"setID\" IN ($l) GROUP BY \"setID\"";
            $res = $this->select_query($sql);
            $offset += $stepsize;
            // For each row, export to binaries_X
            foreach ($res as $arr) {
                $setID = $arr['setID'];

                $subject = $arr['subject'];
                $newsgroup = $groupID;
                $date = $arr['date'];
                $bins = $arr['bins'];
                $size = $arr['totalsize'];

                $set_array = new TableSetData;
                $set_array->setID = $setID;
                $set_array->groupID = $newsgroup;
                $set_array->subject = $subject;  // 1st hit determines the subject, correct.
                // If the subject name is "Some Random Movie [1/104]", then:
                $set_array->articlesmax = get_set_size($subject);	// This is '104'
                $set_array->binaries = $bins;			// And this is the number of files belonging to the set. (hopefully 104).
                $set_array->size = $size;
                $set_array->date = $date;        // 1st hit also determines the set date.

                $this->add_set($set_array);
            }
            $sql = "UPDATE binaries_$groupID SET \"dirty\" = " . CLEAN . " WHERE \"setID\" in ($l)";
            $this->execute_query($sql);
            $t_time = microtime(TRUE);
            $ETA = floor((($total - $cnt) * ($t_time - $s_time) / $cnt));
            update_queue_status($this, $id, NULL, $ETA, floor(85 + ((15 * $cnt) / $total)), NULL, NULL);
        }      
    }
//old but also needed for merge_sets --> rewrite XXX
    private function add_set_data($groupID, $setID_filter = NULL)
    {
        assert (is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $this->escape($groupID);
        if ($setID_filter !== NULL) {
            // we only update one set
            $this->escape($setID_filter, TRUE);
            $Qsetid = " AND binaries_$groupID.\"setID\" = $setID_filter ";
            $Qsetid1 = " AND \"ID\" = $setID_filter ";
        } else
            // we update all sets
            $Qsetid = $Qsetid1 = '';

        // First delete everything for this group or only the specific set:
        $sql = "DELETE FROM setdata WHERE \"groupID\" = '$groupID' $Qsetid1";
        $this->execute_query($sql);

        // Now re-create it.

        $sql = 'SELECT "setID", count("binaryID") AS bins, MIN("subject") AS subject, MIN("date") AS date, SUM("bytes") AS totalsize ' .
            "FROM \"binaries_$groupID\" WHERE 1=1 $Qsetid GROUP BY \"setID\"";
        $res1 = $this->execute_query($sql);

        // To minimise memory requirements, we update setdata per set, instead of all at the end:
        if (is_array($res1)) {
            //   try {
            //        $this->lock(array('setdata' =>'write'));
            foreach ($res1 as $arr) {
                $setID = $arr['setID'];

                $subject = $arr['subject'];
                $newsgroup = $groupID;
                $date = $arr['date'];
                $bins = $arr['bins'];
                $size = $arr['totalsize'];

                $set_array = new TableSetData;
                $set_array->setID = $setID;
                $set_array->groupID = $newsgroup;
                $set_array->subject = $subject;  // 1st hit determines the subject, correct.
                // If the subject name is "Some Random Movie [1/104]", then:
                $set_array->articlesmax = get_set_size($subject);	// This is '104'
                $set_array->binaries = $bins;			// And this is the number of files belonging to the set. (hopefully 104).
                $set_array->size = $size;
                $set_array->date = $date;        // 1st hit also determines the set date.

                $this->add_set($set_array);
            }
            // } catch(exception $e) {
            ///  $this->unlock();
            //      throw $e;
            //  }

            //   $this->unlock();
        }
    }

    private function add_set(TableSetData $set)
    {
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        static $cols = array ('ID', 'groupID', 'subject', 'articlesmax', 'binaries', 'date', 'size');
        $vals = array ($set->setID, $set->groupID, $set->subject, $set->articlesmax, $set->binaries, $set->date, $set->size);
        $this->insert_query('setdata', $cols, $vals, FALSE);
    }

 		
    function quick_expire($groupID)
    {
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $type = USERSETTYPE_GROUP;
        $marking_on = MARKING_ON;
        $keep_int_cfg = get_config($this, 'keep_interesting');
        $time = time();
        // Expire : from days to seconds
        $expire = group_expire($this, $groupID);
        $expire *=  24 * 3600;
        // convert to epochtime:
        $expire = $time - $expire;
        if ($keep_int_cfg)
            $keep_int = " AND \"binaryID\" NOT IN ( SELECT \"binaryID\" FROM usersetinfo JOIN binaries_$groupID AS bin ON bin.\"setID\" = usersetinfo.\"setID\" " 
            . " WHERE \"type\" =  '$type' AND \"statusint\" = '$marking_on') ";
        else 
            $keep_int = '';
        $sql = "DELETE FROM parts_$groupID WHERE \"date\" < $expire $keep_int";
        $res = $this->execute_query($sql);
    }

    function expire_binaries($groupID, $dbid) 
    {
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        assert(is_numeric($groupID) && is_numeric($dbid));
        $type = USERSETTYPE_GROUP;

        $time = time();
        // Expire : from days to seconds
        $expire = group_expire($this, $groupID);
        $expire *=  24 * 3600;
        // convert to epochtime:
        $expire = $time - $expire;
        $do_expire_incomplete = $expire_incomplete = get_config($this, 'expire_incomplete');
        $expire_percentage = get_config($this, 'expire_percentage');
        $expire_incomplete *=  24 * 3600;
        $expire_incomplete = $time - $expire_incomplete;
        $marking_on = MARKING_ON;
        $prefs = load_config($this);

        if ($prefs['keep_interesting'] )
            $keep_int = " AND \"setID\" NOT IN ( SELECT \"setID\" FROM usersetinfo WHERE \"type\" = '$type' AND \"statusint\" = '$marking_on') ";
        else 
            $keep_int = '';

        echo_debug('Deleting expired posts', DEBUG_DATABASE);
        $sql = "SELECT count(\"binaryID\") AS cnt FROM binaries_$groupID WHERE \"date\" < $expire $keep_int";
        $res = $this->execute_query($sql);
        if (is_array($res) === FALSE)
            $cnt = 0;
        else 
            $cnt = $res[0]['cnt'];
        write_log ('Deleting '. $cnt . ' binaries');
        update_queue_status ($this, $dbid, NULL, 0, 1);

        if ($prefs['keep_interesting'] )
            $keep_int  = " AND \"ID\" NOT IN ( SELECT \"setID\" FROM usersetinfo WHERE \"type\" = '$type' AND \"statusint\" = '$marking_on') ";
        else 
            $keep_int = '';
        // first clean all the sets we want to remove
        $Qcomplete = '';
        if ($do_expire_incomplete != 0 && $expire_percentage > 0 && $expire_percentage < 100)
            $Qcomplete = " OR (\"articlesmax\" != 0 AND floor(\"binaries\" * 100 / GREATEST(1, \"articlesmax\")) < '$expire_percentage' AND \"date\" < '$expire_incomplete' ) ";
        $sql = "DELETE FROM setdata WHERE \"groupID\" = '$groupID' AND (\"date\" < $expire $Qcomplete) $keep_int";
        $res = $this->execute_query($sql);
        update_queue_status ($this, $dbid, NULL, 0, 30);

        $sql = "DELETE FROM usersetinfo WHERE \"setID\" NOT IN (SELECT \"ID\" FROM setdata) AND \"type\" = '$type' ";
        $res = $this->execute_query($sql);

        update_queue_status ($this, $dbid, NULL, 0, 40);
        $sql = "DELETE FROM extsetdata WHERE \"setID\" NOT IN (SELECT \"ID\" FROM setdata) AND \"type\" = '$type'";
        // note that this will also remove data about sets that hasn't been received yet, but typically, expire runs after an update, so all data should be in.
        $res = $this->execute_query($sql);

        update_queue_status ($this, $dbid, NULL, 0, 50);
        $sql = "DELETE FROM merged_sets WHERE \"new_setid\" NOT IN (SELECT \"ID\" FROM setdata) AND \"type\" = '$type' ";
        // see above
        $res = $this->execute_query($sql);

        update_queue_status ($this, $dbid, NULL, 0, 60);

        if ($prefs['keep_interesting'] )
            $keep_int  = " AND \"setID\" NOT IN ( SELECT \"setID\" FROM usersetinfo WHERE \"type\" =  '$type' AND \"statusint\" = '$marking_on') ";
        else 
            $keep_int = '';

        $sql = "DELETE FROM binaries_$groupID WHERE \"setID\" NOT IN (SELECT \"ID\" FROM setdata WHERE \"groupID\" = $groupID) $keep_int";
        $res = $this->execute_query($sql);

        update_queue_status ($this, $dbid, NULL, 0, 85);

        if ($prefs['keep_interesting'] )
            $keep_int = " AND \"binaryID\" NOT IN ( SELECT \"binaryID\" FROM usersetinfo JOIN binaries_$groupID AS bin ON bin.\"setID\" = usersetinfo.\"setID\" " 
            . " WHERE \"type\" = '$type' AND \"statusint\" = '$marking_on') ";
        else 
            $keep_int = '';
        $sql = "DELETE FROM parts_$groupID WHERE \"binaryID\" NOT IN (SELECT \"binaryID\" FROM binaries_$groupID) $keep_int";
        $res = $this->execute_query($sql);

        echo_debug("Deleted {$cnt} binaries", DEBUG_DATABASE);
        update_queue_status ($this, $dbid, NULL, 0, 95);

        $this->update_postcount($groupID);
        update_queue_status ($this, $dbid, NULL, 0, 100);
        return $cnt;
    }
    function update_postcount($groupID)
    {
        $sql = "UPDATE groups SET postcount =(SELECT COUNT(\"ID\") FROM parts_{$groupID}), \"extset_update\"='0' WHERE \"ID\" = $groupID ";
        $this->execute_query($sql);
    }


    function purge_binaries($groupID) 
    {
        assert (is_numeric($groupID));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $active = group_subscribed($this, $groupID);

        echo_debug('Deleting all posts', DEBUG_DATABASE);

        $type = USERSETTYPE_GROUP;
        $sql = "DELETE FROM usersetinfo WHERE \"setID\" in (SELECT \"ID\" FROM setdata WHERE \"groupID\" = '$groupID') AND \"type\" = '$type'";
        $res = $this->execute_query($sql);

        $sql = "DELETE FROM extsetdata WHERE \"setID\" in (SELECT \"ID\" FROM setdata WHERE \"groupID\" = '$groupID') AND \"type\" = '$type'";
        $res = $this->execute_query($sql);

        $sql = "DELETE FROM merged_sets WHERE \"new_setid\" in (SELECT \"ID\" FROM setdata WHERE \"groupID\" = '$groupID') AND \"type\" = '$type'";
        $res = $this->execute_query($sql);

        $sql = "DELETE FROM setdata WHERE \"groupID\" = '$groupID'";
        $res = $this->execute_query($sql);
        if ($active === TRUE) {
            $sql = "TRUNCATE TABLE \"parts_$groupID\"";
            $res = $this->execute_query($sql);
            $sql = "TRUNCATE TABLE \"binaries_$groupID\"";
            $this->execute_query($sql);
        }
        $res = $this->execute_query("UPDATE groups SET \"last_record\"=0, \"first_record\"=0, \"mid_record\"=0, \"last_updated\"=NULL, \"postcount\" =0 WHERE \"ID\" = '$groupID' ");
        echo_debug('Purged all binaries', DEBUG_DATABASE);
    }

    function subscribe($groupid, $expire)
    {
        assert (is_numeric($groupid) && is_numeric($expire));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $is_subscribed = group_subscribed($this, $groupid);
        if ($is_subscribed !== FALSE)
            throw new exception('Already subscribed', DB_FAILURE);
        try {
            $qry = "DROP TABLE IF EXISTS \"binaries_$groupid\"";
            $this->execute_query($qry);
            $qry = "DROP TABLE IF EXISTS \"parts_$groupid\"";
            $this->execute_query($qry);
            unset($qry);
            switch($this->databasetype) {
            case 'mysql':
            case 'mysqlt':
            case 'mysqli':
                $qry[] = "DROP TABLE IF EXISTS \"binaries_$groupid\"";
                $qry[] = "CREATE TABLE \"binaries_$groupid\" ( "
                    .    "\"binaryID\" char(32) NOT NULL, "
                    .    "\"subject\" varchar(512) NOT NULL default '', " 
                    .    "\"fromname\" varchar(512) NOT NULL default '', "
                    .    "\"date\" int(16) unsigned NOT NULL default '0', "
                    .    "\"bytes\" bigint(16) unsigned NOT NULL default '0', "
                    .    "\"totalParts\" int(16) unsigned NOT NULL default '0', "
                    .    "\"setID\" varchar(50) NOT NULL default '0', "
                    .    "\"dirty\" tinyint(1) unsigned NOT NULL default '0', "
                    .    "PRIMARY KEY (\"binaryID\"), "
                    .    "KEY \"setID_idx\" (\"setID\") "
                    //.    ") ENGINE=MyISAM DEFAULT CHARSET=utf8 pack_keys=1 COLLATE=utf8_general_ci";
                    .    ") ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci";
                $qry[] = "DROP TABLE IF EXISTS \"parts_$groupid\"";
                $qry[] = "CREATE TABLE \"parts_$groupid\" ("
                    .    "\"ID\" bigint(16) unsigned NOT NULL auto_increment,"
                    .    "\"binaryID\" char(32) NOT NULL default '',"
                    .    "\"messageID\" varchar(255) NOT NULL default '',"
                    .    "\"subject\" varchar(512) NOT NULL default '',"
                    .    "\"fromname\" varchar(512) NOT NULL default '',"
                    .    "\"date\" int(16) unsigned NOT NULL default '0',"
                    .    "\"partnumber\" bigint(16) unsigned NOT NULL default '0',"
                    .    "\"size\" bigint(16) unsigned NOT NULL default '0',"
                    .    "\"dirty\" tinyint(1) unsigned NOT NULL default '0'," 
                    .    "PRIMARY KEY (\"ID\"),"
                    .    "KEY \"binaryID_idx\" (\"binaryID\"),"
                    .    "KEY \"date_idx\" (\"date\"),"
                    .    "KEY \"dirty_idx\" (\"dirty\")"
                    //.    ") ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 pack_keys=1 COLLATE=utf8_general_ci";
                    .    ") ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  COLLATE=utf8_general_ci";
                break;
            case 'postgres8':
            case 'postgres7':
                $this->execute_if_exists("binaries_$groupid", "DROP TABLE \"binaries_$groupid\"");
                $qry[] = "CREATE TABLE \"binaries_$groupid\" ("
                    . " \"binaryID\" character(32) NOT NULL,"
                    . " \"subject\" character varying(512) DEFAULT '' NOT NULL,"
                    . " \"fromname\" character varying(512) DEFAULT '' NOT NULL,"
                    . " \"date\" bigint DEFAULT '0' NOT NULL,"
                    . " \"bytes\" bigint DEFAULT '0' NOT NULL,"
                    . " \"totalParts\" bigint DEFAULT '0' NOT NULL,"
                    . " \"setID\" character varying(50) DEFAULT '0' NOT NULL,"
                    . " \"dirty\" smallint DEFAULT '0' NOT NULL, " 
                    . " PRIMARY KEY (\"binaryID\")"
                    . ")";
                $qry[] = "CREATE INDEX \"setID_{$groupid}_idx\" on \"binaries_$groupid\" (\"setID\")";
                $this->execute_if_exists("parts_$groupid", "DROP TABLE \"parts_$groupid\"");

                $qry[] = "CREATE TABLE \"parts_$groupid\" ("
                    . " \"ID\" bigserial NOT NULL,"
                    . " \"binaryID\" character(32) DEFAULT '' NOT NULL,"
                    . " \"messageID\" character varying(255) DEFAULT '' NOT NULL,"
                    . " \"subject\" character varying(512) DEFAULT '' NOT NULL,"
                    . " \"fromname\" character varying(512) DEFAULT '' NOT NULL,"
                    . " \"date\" bigint DEFAULT '0' NOT NULL,"
                    . " \"partnumber\" bigint DEFAULT '0' NOT NULL,"
                    . " \"size\" bigint DEFAULT '0' NOT NULL,"
                    . " \"dirty\" smallint DEFAULT '0' NOT NULL, " 
                    . " PRIMARY KEY (\"ID\")"
                    . ")";
                $qry[] = "CREATE INDEX \"binaryID_{$groupid}_idx\" on \"parts_$groupid\" (\"binaryID\")";
                $qry[] = "CREATE INDEX \"dirty_{$groupid}_idx\" on \"parts_$groupid\" (\"dirty\")";
                $qry[] = "CREATE INDEX \"date_{$groupid}_idx\" on \"parts_$groupid\" (\"date\")";
                break;
            case 'sqlite':
            default:
                throw new exception ('Database type no supported');
            }
            foreach($qry as $k=>$q) {
                $this->execute_query($q);
            }
        } catch (exception $e) {
            throw new exception('Cannot create table: ' . $e->getMessage() . '-' . $this->DB->ErrorMsg() . '(' . $this->DB->ErrorNo() . ')', DB_FAILURE);
        }
        try { 
            update_group_state($this, $groupid, NG_SUBSCRIBED, $expire);
        } catch (exception $e) {
            throw new exception('Subscribe failed: ' . $this->DB->ErrorMsg() . '(' . $this->DB->ErrorNo() . ')', DB_FAILURE);
        }
        return TRUE;
    }

    function unsubscribe($groupid) // set to inactive an remove the binaries table
    {
        assert (is_numeric($groupid));
        echo_debug_function(DEBUG_DATABASE, __FUNCTION__);
        $is_subscribed = group_subscribed($this, $groupid);
        if ($is_subscribed === FALSE)
            throw new exception('Not subscribed', DB_FAILURE);
        try {
            $expire = get_config($this, 'default_expire_time');
            update_group_state($this, $groupid, NG_UNSUBSCRIBED, $expire);
        } catch (exception $e) {
            throw new exception('Unsubscribe failed: ' . $this->DB->ErrorMsg() . '(' . $this->DB->ErrorNo() . ')', DB_FAILURE);
        }

        try {
            $qry = "DROP TABLE IF EXISTS \"binaries_$groupid\"";
            $this->execute_query($qry);
            $qry = "DROP TABLE IF EXISTS \"parts_$groupid\"";
            $this->execute_query($qry);
        } catch (exception $e) {
            throw new exception('Cannot drop table: ' . $this->DB->ErrorMsg() . '(' . $this->DB->ErrorNo() . ')', DB_FAILURE);
        }
        // Also mark as 'clean' in group table, otherwise re-subscribe uses bad last_record info.
        $res = $this->execute_query("UPDATE groups SET \"last_record\"=0, \"last_updated\"=NULL WHERE \"ID\" = '$groupid' ");
        return TRUE;
    }
    function update_binary_info($group_id, $group_name, $do_expire, $expire, action $item)
    {
        assert (is_numeric($group_id) && is_numeric($expire));
        // Update binary info:
        write_log('Updating binary info for ' . $group_name, LOG_NOTICE);
        $this->update_binary_data($group_id, $item->get_dbid());
        update_queue_status($this, $item->get_dbid(), NULL, 0, 75, 'Added binary data');
        $this->merge_binary_sets($group_id);
        update_queue_status($this, $item->get_dbid(), NULL, 0, 85, 'Merged binary sets');

        // Also update set info:
        write_log('Updating set info for ' . $group_name, LOG_NOTICE);
        $this->update_set_data($group_id, $item->get_dbid());
        update_queue_status($this, $item->get_dbid(), NULL, 0, 99, 'Added set data');
        write_log('Updating set info for ' . $group_name . ' complete', LOG_NOTICE);
    }
/*
    function update_binary_info_old($group_id, $group_name, $do_expire, $expire, action $item)
    {
        assert (is_numeric($group_id) && is_numeric($expire));
        // Update binary info:
     //   if ($do_expire) {	
     //       $sql = "DELETE FROM parts_$group_id WHERE \"date\" < $expire";
     //       $res = $this->execute_query($sql);
     //}
        write_log('Updating binary info for ' . $group_name, LOG_NOTICE);
        $this->add_binary_data($group_id);
        update_queue_status($this, $item->get_dbid(), NULL, 0, 30, 'Added binary data');
        $this->merge_binary_sets($group_id);
        update_queue_status($this, $item->get_dbid(), NULL, 0, 60, 'Merged binary sets');

        // Also update set info:
        write_log('Updating set info for ' . $group_name, LOG_NOTICE);
        $this->add_set_data($group_id);
        update_queue_status($this, $item->get_dbid(), NULL, 0, 90, 'Added set data');
        write_log('Updating set info for ' . $group_name . ' complete', LOG_NOTICE);
}
 */
    function merge_binary_sets($group_id)
    {
        $this->escape($group_id);
        $sql = "SELECT merged_sets.\"new_setid\", binaries_$group_id.\"setID\" AS old_setid FROM binaries_$group_id " 
            . "JOIN merged_sets ON merged_sets.\"old_setid\" = binaries_$group_id.\"setID\" AND merged_sets.\"type\" = '" . USERSETTYPE_GROUP . "'";

        $res = $this->execute_query($sql);
        if ($res === FALSE) return;
        foreach ($res as $row) {
            $new_setid = $row['new_setid'];
            $old_setid = $row['old_setid'];
            $this->escape($new_setid, TRUE);
            $this->escape($old_setid, TRUE);
            $sql = "UPDATE binaries_$group_id SET \"setID\" = $new_setid WHERE \"setID\" = $old_setid";
            $this->execute_query($sql);
        }
    }

    function merge_sets($setid1, array $setids)
    {
        echo_debug_function(DEBUG_SERVER, __FUNCTION__);
        $osetid = $setid1; // new set id
        try {
            $groupid1 = get_groupid_for_set($this, $setid1);
        } catch (exception $e) {
            write_log("Cannot find group for base set $setid1", LOG_INFO);
            return; 
        }
        $this->escape($setid1, TRUE);
        foreach ($setids as $setid2) { // all old setids
            if (trim($setid2) == '') continue;
            try {
                $groupid2 = get_groupid_for_set($this, $setid2);
            } catch (exception $e) {
                write_log("Cannot find group for merging set $setid2", LOG_INFO);
                continue;
            }
            if ($groupid1 != $groupid2)
                throw new exception ('Groups don\'t match');
            $osetid2 = $setid2;
            $this->escape($setid2, TRUE);

            $sql = "UPDATE binaries_$groupid1 SET \"setID\" = $setid1 WHERE \"setID\" = $setid2";
            $this->execute_query($sql);
            $sql = "DELETE FROM setdata WHERE \"ID\" = $setid2";
            $this->execute_query($sql);
            $dummy = array();
            store_merge_sets_data($this, $osetid, $osetid2, USERSETTYPE_GROUP, ESI_NOT_COMMITTED);
        }
        $this->add_set_data($groupid1, $osetid);
    }
}


?>
